﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// Summary description for DBReader
/// </summary>
public class DBReader : IDataReaderFactory
{
    private string connectionString;
    private static DBReader dbrInstance;


    protected DBReader(string ConnectionStringName)
	{
        connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString;
	}

    public static DBReader GetDBReaderInstance(string ConnectionStringName)
    {
        if (dbrInstance == null)
        {
            dbrInstance = new DBReader(ConnectionStringName);
        }

        return dbrInstance;
    }

    public DataTable GetDataTable(string StoredProcedureName, CommandType cmdType = CommandType.StoredProcedure, params MyDBParameter[] parameter)
    {
        DataTable dt = new DataTable();
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand(StoredProcedureName, conn))
            {
                cmd.CommandType = cmdType;

                if (parameter.Length > 0)
                {
                    for (int i = 0; i < parameter.Length; i++)
                    {
                        SqlParameter p = new SqlParameter(parameter[i].ParameterName, parameter[i].Value);
                        p.Direction = parameter[i].Direction;
                        cmd.Parameters.Add(p);
                    }
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }
        }

        return dt;
    }

    public int ExecuteNonQuery(string StoredProcedureName, CommandType cmdType = CommandType.StoredProcedure, params MyDBParameter[] parameter)
    {
        int n = 0;
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand(StoredProcedureName, conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                if (parameter.Length > 0)
                {
                    for (int i = 0; i < parameter.Length; i++)
                    {
                        SqlParameter p = new SqlParameter(parameter[i].ParameterName, parameter[i].Value);
                        p.Direction = parameter[i].Direction;
                        cmd.Parameters.Add(p);
                    }
                }

                conn.Open();
                n = cmd.ExecuteNonQuery();
                conn.Close();
            }
        }

        return n;
    }

    public object ExecureScalar(string StoredProcedureName, CommandType cmdType = CommandType.StoredProcedure, params MyDBParameter[] parameter)
    {
        object objOuput = null;
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand(StoredProcedureName, conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                if (parameter.Length > 0)
                {
                    for (int i = 0; i < parameter.Length; i++)
                    {
                        SqlParameter p = new SqlParameter(parameter[i].ParameterName, parameter[i].Value);
                        p.Direction = parameter[i].Direction;
                        cmd.Parameters.Add(p);
                    }
                }

                conn.Open();
                objOuput = cmd.ExecuteScalar();
                conn.Close();
            }
        }
        return objOuput;
    }
}